import openpyxl
import xlrd
import time
import pandas as pd


# maxnrows=100

#获取毒物所有症状
def read_title():
    lst_title=[]
    wk = openpyxl.load_workbook('duwuzhengzhuang.xlsx')
    sheet = wk.active
    rows = sheet.max_row
    cols = sheet.max_column
    for i in range(2, rows + 1):
        for j in range(1,cols+1):
            size = sheet.cell(i,j).value
            lst_title.append(size)
    new_title = [x for x in lst_title if x != None]
    return new_title

#将文字进行分词，提取关键字
def get_word(str):
    lst_word=[]
    for item in lst_title:
        if (str.find(item) != -1):
            lst_word.append(item)
    return lst_word

def get_name():
    name_lst = []
    # 毒物症状描述
    content_lst = []
    wk = xlrd.open_workbook('duwuxiangqing.xls')
    table = wk.sheet_by_name('Sheet1')
    rows = table.nrows

    for i in range(2, rows):
        name = table.cell(i, 2).value
        str = table.cell(i, 17).value + table.cell(i, 18).value
        content_lst.append(str)
        name_lst.append(name)
    return name_lst

def get_desprition():
    content_lst=[]
    wk = xlrd.open_workbook('duwuxiangqing.xls')
    table = wk.sheet_by_name('Sheet1')
    rows = table.nrows

    for i in range(2, rows):
        str = table.cell(i, 17).value + table.cell(i, 18).value
        content_lst.append(str)
    return content_lst

#得到毒物对应的症状关键字
def get_posion(lst):
    #毒物名字
    name_lst=[]
    #毒物症状描述
    content_lst=[]
    wk = xlrd.open_workbook('duwuxiangqing.xls')
    table = wk.sheet_by_name('Sheet1')
    rows = table.nrows

    for i in range(2,rows):
        name=table.cell(i,2).value
        str=table.cell(i,17).value+table.cell(i,18).value
        content_lst.append(str)
        name_lst.append(name)
    #结果集合，第一个是毒物名称，剩余为毒物对应的症状
    ans_lst=[]
    for item in name_lst:
        k=[]
        k.append(item)
        ans_lst.append(k)
    for item in lst:
        i = 0
        for str in content_lst:
            if(str.find(item)>=0):
                ans_lst[i].append(item)
            i+=1
    return ans_lst

if __name__ == '__main__':
    lst_title=read_title()
    name=[]
    name=get_name()
    desprition=get_desprition()
    #print(len(name))

    first=0
    five=0
    nosimple=0

    fives=[]
    nofives=[]


    print("毒物总数："+str(len(name)))
    for i in range(len(name)):
        strname=name[i]
        #print(strname)
        strdesprition=desprition[i]
        #1进行分词
        lst_word = get_word(strdesprition)
        #2.1获取毒物描述对应的关键字
        lst_posion = get_posion(lst_title)
        #print(lst_posion)
        # 2.2构建毒物匹对分词的个数
        temp = {}
        # 2.3开始匹对

        for item in lst_posion:
            #print(item)
            if len(item) > 1:
                k = 0
                for sample in item:
                    for st in lst_word:
                        if sample == st:
                            k = k + 1
                            break
                names = item[0]
                if ((names in temp.keys())==False):
                    temp[names] = k
                elif temp[names] < k:
                    temp[names] = k
        ans = sorted(temp.items(), key=lambda kv: (kv[1], kv[0]), reverse=True)
        # 输出
        #print("匹对毒物排行前5：")
        #print(len(ans))

        l = 1
        for item in ans:
            if item[0]==strname:
                if l==1:
                    first=first+1
                    print("匹配上Top1："+item[0])
                elif l<=5:
                    five=five+1
                    print("匹配上Top5：" + item[0])
                elif l>5:
                    print("匹配上Top5之外：" + item[0])
                    fives.append(item[0])
                    #print(strname)
                break
            l = l + 1
        if l==len(ans)+1:
            print("未匹对上："+strname)
            nofives.append(strname)
            nosimple=nosimple+1

    print("Top5之外的毒物有："+str(fives))
    print("完全未匹对上的毒物有："+str(nofives))
    print("Top1的有："+str(first)+" Top5的有："+str(five+first)+" Top5之外的有："+str(len(fives))+" 完全未匹对有："+str(len(nofives)))
    sum=first+five+len(fives)
    print("Top1准确率："+str(first*1.0/sum))
    print("Top5准确率：" + str((five+first) * 1.0 / sum))